package com.housekeeping.housekeeping.serviceItem.dao.Impl;

import com.housekeeping.housekeeping.serviceItem.controller.ServiceItemDetailsController;
import com.housekeeping.housekeeping.serviceItem.dao.ServiceItemDao;
import com.housekeeping.housekeeping.serviceItem.entity.ServiceItemDetails;
import com.housekeeping.housekeeping.serviceItem.entity.ServiceItemPage;
import com.housekeeping.housekeeping.serviceItem.utils.IDworkUtils;
import com.housekeeping.housekeeping.utils.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ServiceItemDaoImpl implements ServiceItemDao {
    @Override
    public int add(String aid,ServiceItemPage itemPage, String detailsContext) {
        Connection connection = null;
        PreparedStatement ps = null;
        int res = 0;
        try {
            connection = DBManager.getConnection();
            //开启手动事务管理
            connection.setAutoCommit(false);
            //生成服务详情id
            String detailsId = IDworkUtils.createRandom();
            //生成服务配置id
            String pageId = IDworkUtils.createRandom();
            //写入serviceItem表
            String itemSql = "insert into service_item(detail_id, page_id,a_id) VALUES(?,?,?)";
            ps = connection.prepareStatement(itemSql);
            ps.setString(1, detailsId);
            ps.setString(2, pageId);
            ps.setString(3,aid);
            res = ps.executeUpdate();
            DBManager.close(ps);
            //插入失败回滚
            if (res != 1) {
                throw new RuntimeException();
            }
            //写入details表
            String detailSql = "insert into service_item_details(id, context) VALUES(?,?)";
            ps = connection.prepareStatement(detailSql);
            ps.setString(1, detailsId);
            ps.setString(2, detailsContext);
            res = ps.executeUpdate();
            DBManager.close(ps);
            //插入失败回滚
            if (res != 1) {
                throw new RuntimeException();
            }
            //写入page表
            String pageSql = "insert into service_item_page(id, title, titlePage_img_url, price_unit, price_one_hour, price_employee, price_once, weekend_is_work, tag, start_work, off_work, set_valid_time, set_invalid_time) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
            ps = connection.prepareStatement(pageSql);
            ps.setString(1, pageId);
            ps.setString(2, itemPage.getTitle());
            ps.setString(3, itemPage.getTitlePageImgUrl());
            ps.setInt(4, itemPage.getPriceUnit());
            ps.setInt(5, itemPage.getPriceOneHour());
            ps.setInt(6, itemPage.getPriceEmployee());
            ps.setInt(7, itemPage.getPriceOnce());
            ps.setInt(8, itemPage.getWeekendWork());
            ps.setInt(9, itemPage.getTag());
            ps.setString(10, itemPage.getStartWork());
            ps.setString(11, itemPage.getOffWork());
            ps.setString(12, itemPage.getSetValidTime());
            ps.setString(13, itemPage.getSetIValidTime());
            res = ps.executeUpdate();
            //插入失败回滚
            if (res != 1) {
                throw new RuntimeException();
            }
            //成功，提交
            connection.commit();
        } catch (SQLException troubles) {
            troubles.printStackTrace();
            //异常事务回滚
            try {
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }finally {
            //回收资源
            DBManager.close(ps,connection);
        }
        return res;
    }

    @Override
    public ServiceItemPage getItemPage(String aid) throws Exception {
        ServiceItemPage res = null;
        //获取连接
        Connection connection = DBManager.getConnection();
        //查询对应aid，service_item_page表下所有字段
        String sql = "select  title, titlePage_img_url, price_unit, price_one_hour, price_employee, price_once, weekend_is_work, tag, start_work, off_work, set_valid_time, set_invalid_time from service_item s,service_item_page p where s.a_id=? and s.page_id=p.id";
        //获取预处理对象
        PreparedStatement ps = connection.prepareStatement(sql);
          //预处理
            ps.setString(1,aid);
        //查询结果集
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next()) {
            String title = resultSet.getString(1);
            String titlePage_img_url = resultSet.getString(2);
            int price_unit = resultSet.getInt(3);
            int price_one_hour = resultSet.getInt(4);
            int price_employee = resultSet.getInt(5);
            int price_once = resultSet.getInt(6);
            int weekend_is_work = resultSet.getInt(7);
            int tag = resultSet.getInt(8);
            String start_work = resultSet.getString(9);
            String off_work = resultSet.getString(10);
            String set_valid_time = resultSet.getString(11);
            String set_invalid_time = resultSet.getString(12);
            res = new ServiceItemPage(start_work, off_work, price_employee, price_once, price_one_hour, price_unit, set_valid_time, set_invalid_time, tag, title, titlePage_img_url, weekend_is_work);
        }
        //关闭资源
        DBManager.close(resultSet, ps, connection);
        return res;
    }

    @Override
    public ServiceItemDetails getItemDetails(String aid) throws SQLException {
        ServiceItemDetails res=null;
        //获取连接
        Connection connection=DBManager.getConnection();
        //sql:查询对应aid,service_item_details表详情字段
        String sql="select context from service_item_details d,service_item s where s.a_id=? and s.detail_id=d.id";
        //获取预处理对象
        PreparedStatement ps=connection.prepareStatement(sql);
          //预处理
            ps.setString(1,aid);
        //查询结果集
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next())
        {
            res=new ServiceItemDetails(resultSet.getString(1));
        }
        //回收资源
        DBManager.close(resultSet,ps,connection);
        return res;
    }

    @Override
    public int update(String aid,ServiceItemPage itemPage, String details) {
        Connection connection=null;
        PreparedStatement ps=null;
        int res=0;
        try {
            //获取连接
            connection=DBManager.getConnection();
            //开启事物
            connection.setAutoCommit(false);
            //进行详情更新
            //查询 details，page表的id
            String idSql="select detail_id,page_id from service_item where a_id=?";
            ps=connection.prepareStatement(idSql);
            ps.setString(1,aid);
            ResultSet resultSet = ps.executeQuery();
            String pid=null;
            String did=null;
            if (resultSet.next())
            {
                did=resultSet.getString(1);
                pid=resultSet.getString(2);
            }
            DBManager.close(resultSet,ps);
            if (pid==null||did==null){throw new RuntimeException(aid+"没有服务项可更新");}
            //写入details表
            String detailSql = "update service_item_details set context=? where id=? ";
            ps = connection.prepareStatement(detailSql);
            ps.setString(1, details);
            ps.setString(2,did);
            res=ps.executeUpdate();
            DBManager.close(ps);
            //插入失败回滚
            if (res != 1) { throw new RuntimeException(); }
            //写入page表
            String pageSql = "update  service_item_page set title=?, titlePage_img_url=?, price_unit=?, price_one_hour=?, price_employee=?, price_once=?, weekend_is_work=?, tag=?, start_work=?, off_work=?, set_valid_time=?, set_invalid_time=? where id=?";
            ps = connection.prepareStatement(pageSql);
            ps.setString(1, itemPage.getTitle());
            ps.setString(2, itemPage.getTitlePageImgUrl());
            ps.setInt(3, itemPage.getPriceUnit());
            ps.setInt(4, itemPage.getPriceOneHour());
            ps.setInt(5, itemPage.getPriceEmployee());
            ps.setInt(6, itemPage.getPriceOnce());
            ps.setInt(7, itemPage.getWeekendWork());
            ps.setInt(8, itemPage.getTag());
            ps.setString(9, itemPage.getStartWork());
            ps.setString(10, itemPage.getOffWork());
            ps.setString(11, itemPage.getSetValidTime());
            ps.setString(12, itemPage.getSetIValidTime());
            ps.setString(13, pid);
            res = ps.executeUpdate();
            //插入失败回滚
            if (res != 1) {throw new RuntimeException(); }
            //成功，提交
            connection.commit();
        }catch (Exception e)
        {
            try {
                connection.rollback();
            } catch (SQLException troubles) {
                troubles.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            DBManager.close(connection,ps);
        }
        return res;
    }

    @Override
    public int accountServiceItemExist(String aid) throws SQLException {
        int count=0;
        Connection connection=DBManager.getConnection();
        //获取serviceItem表中用户已有服务项数
        String sql="select count(*) from service_item where a_id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1,aid);
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next()){count=resultSet.getInt(1);}
        DBManager.close(resultSet,ps,connection);
        return count;
    }
}
